package com.aspire.core;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.ArrayUtils;

import com.aspire.core.match.ua.object.Device;
import com.aspire.core.match.ua.object.DeviceBrand;
import com.aspire.core.match.ua.object.DeviceGWUA;
import com.aspire.core.match.ua.object.DeviceKeyword;
import com.aspire.core.match.ua.object.DeviceOS;
import com.aspire.core.match.ua.object.DeviceUA;
import com.aspire.core.match.ua.object.FilterDevice;
import com.aspire.core.match.ua.object.Series;
import com.aspirecn.core.ShareCommon;
import com.csvreader.CsvReader;

import junit.framework.Test;
import junit.framework.TestCase;
import junit.framework.TestSuite;

public class ImportUAToDB extends TestCase {
	/**
	 * Create the test case
	 * 
	 * @param testName
	 *            name of the test case
	 */
	public ImportUAToDB(String testName) {
		super(testName);
	}

	/**
	 * @return the suite of tests being tested
	 */
	public static Test suite() {
		return new TestSuite(ImportUAToDB.class);
	}

	/**
	 * Rigourous Test :-)
	 * 
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws ConfigurationException
	 * @throws NoSuchMethodException 
	 * @throws InvocationTargetException 
	 * @throws InstantiationException 
	 * @throws IllegalAccessException 
	 * @throws IOException 
	 */
	public void testApp() throws ConfigurationException,
			ClassNotFoundException, SQLException, IllegalAccessException, InstantiationException, InvocationTargetException, NoSuchMethodException, IOException {

		toDB();
	}

	// 已经生成的json导入数据库
	private void toDB() throws ConfigurationException, ClassNotFoundException,
			SQLException, IllegalAccessException, InstantiationException, InvocationTargetException, NoSuchMethodException, IOException {
				
		Connection conn = this.getConnection();
		QueryRunner qr = new QueryRunner();
		
		CsvReader reader = new CsvReader("D:\\Temp\\uamatchlog\\uamatchlog.csv",
				',', Charset.forName("gbk"));
		String sql = "insert into uamatchlog(ua,newid,newname,oldid,oldname,keywords,times)"
				+ " values(?,?,?,?,?,?,?)";
		
		while (reader.readRecord()) {
			
			Object[] param = new Object[]{reader.get(0),reader.get(1),reader.get(2),reader.get(3),reader.get(4),reader.get(5),reader.get(6)};
			
			try
			{
				qr.update(conn, sql, param);
			}
			catch( Exception e )
			{
				e.printStackTrace();
			}
		}
		
//		Object[][] objs = null;
//		
//		// 导入品牌
//		List<DeviceBrand> deviceBrandList = ShareCommon.getInstance()
//				.getUACache().getDeviceBrandList();
//
//		System.out.println("t_device_brand:" + deviceBrandList.size());
//		sql = "insert into " + table_pre + "t_device_brand(brand_id,brand_name,brand_desc,english_brand_name)"
//				+ " values(?,?,?,?)";
//
//		objs = null;
//		for (DeviceBrand deviceBrand : deviceBrandList) {
//
//			objs = ArrayUtils
//					.add(objs,
//							new Object[] {
//									deviceBrand.getBrand_id(),
//									StringUtils.trimToEmpty(deviceBrand
//											.getBrand_name()),
//									StringUtils.trimToEmpty(deviceBrand
//											.getBrand_desc()),
//									StringUtils.trimToEmpty(deviceBrand
//											.getEnglish_brand_name()) });
//
//		}
//
//		qr.batch(conn, sql, objs);// 批量提交
		
		DbUtils.close(conn);
	}

	private Connection m_conn = null;

	public Connection getConnection() throws ClassNotFoundException,
			ConfigurationException, SQLException {
		if (m_conn == null) {

			PropertiesConfiguration config = new PropertiesConfiguration(
					"database.properties");

			Class.forName(config.getString("db.driver"));
			Connection conn = DriverManager.getConnection(
					config.getString("db.connUrl"),
					config.getString("db.username"),
					config.getString("db.password"));
			m_conn = conn;
		}

		return m_conn;
	}
}
